Juntando Bancos de Dados

Facilitar a sua própria pesquisa

Table of Contents

Unidades de Análise

A maioria de projetos exigem a combinação de dados de fontes diversas. É assim que criamos a nossa própria contribuição para a pesquisa. Por exemplo, para estudar o efeito de um programa de governo sobre a votação, preciseramos de pelo menos dois bancos de dados, um com os dados da localização do programa e outro sobre os resultados eleitorais. Como podemos juntar estes dois bancos para facilitar a nossa análise?

Primeiro, temos que entender os dois bancos: (i) qual é a unidade de análise de cada observaçao? e (ii) quais são as variáveis chaves que identificam unicamente cada observação (evitando duplicações)?

No exemplo acima, pode ser que o programa do governo foi executado em alguns municípios e não em outros, então o nosso banco varia por município, identificado unicamente com a variável do código municipal do IBGE. Por outro lado, imagine que temos os dados eleitorais apenas dos prefeitos e queremos avaliar a margem de vitória, então a unidade de análise é cada município, identificado pelo código municipal do IBGE.

Quando os nossos dois bancos têm a mesma unidade de análise e as mesmas variáveis, a junção dos bancos é mais ou menos fácil. Usamos as variáveis de identificador único e identico para adicionar as colunas de um banco para o outro.

Imagine agora que o banco de dados do programa do governo varia por estado, então ele tem apenas 27 observações, identificadas por sigla do estado (“AC” etc.). Neste caso em que a unidade de análise e as variáveis variam entre bancos, como é possível juntar os bancos? Primeiro, temos que decidir a unidade de análise desejada do banco de dados final - por estado ou por município? Se quissemos uma tabela por estado, temos que agregar os dados eleitorais do nível municipal para o nível estadual com as ferramentas já conhecidas (%>% group_by() %>% summarize()).

Se quissemos uma tabela final por município, não precisa muita preparação mas temos que reconhecer que juntando os dois bancos vai duplicar os dados estaduais para cada município no estado. Por exemplo, a presença do programa indicado por código ‘1’ em “RS” será reptido para cada município de RS. Isto faz sentido neste contexto, mas é crucial entender o que está acontecendo.

Porém, existe uma outra dificuldade com qualquer uma das duas junções - o identificador único de estados (“RS”) não é ígual ao identificador único dos municípios (“4314902”). A informação do estado está presente no código de município, e podemos criar o identificador se conhecemos bem os nossos dados, mas o trabalho para tornar os identificadores identicos é o nosso: Temos que separar os primeiros dois dígitos que indicam o estado, e depois mapear os dígitos para a sigla. O resutlado será uma nova variável, a sigla do estado, para cada município que é compatível com o outro banco de dados. Apenas no final dessa preparação é possível juntar os dois bancos.

Exemplos de Joins Simples (left_join)

Até agora, só discutimos os passos preparatórios:
(1) Decidir a unidade de análise de banco de dados final,
(2) Agregar os bancos de dados para a unidade de análise desejado se necessário, (3) Transformar as variáveis para que temos um identificador comum entre os banco de dados.

O próximo passo é juntar os dois bancos. Temos que definir três argumentos: Os nomes dos dois bancos de dados, e o conjunto de variáveis (a ‘chave’) que usaremos para realizar a junção. Sempre começamos com o banco de dados que já tem a unidade de análise desejado no banco de dados final, e encaminhamos este banco para a função left_join(). Dentro de left_join especificamos o segundo banco de dados, e as variáveis chave (num vetor se for mais que uma variável).


Banco_1 %>% left_join(Banco_2, c("Identificador Comum"))

Para praticar, vamos abrir um outro banco do conjunto de dados no pacote nycflights13, sobre os aviões mesmo, planes. A unidade de flights é um voo (uma partida de Nova Iorque) e à unidade de planes é um avião, repetida várias vezes em flights. A variável de identificar comum é tailnum.

Qual unidade de análise queremos na tabela final? Uma opção é por avião, resumindo por exemplo o número de viagens de cada avião, que exige uma agregação de banco flights por tailnum:


flights_por_aviao <- flights %>% 
  group_by(tailnum) %>%
  tally()

planes_com_num_viagens <- planes %>% left_join(flights_por_aviao, by=c("tailnum"))

Como aparece o resultado final? O planes agora tem mais uma coluna com o número de viagens de cada avião - poderoso, né?

A outra opção é gerar um novo tibble com cada observação um voo, e adicionando dados da avião ao tabela de flights.


flights_com_planes <- flights %>% left_join(planes, by=c("tailnum"))

Vamos filtrar e ordenar os nossos dados para ver que os dados da avião são repetidas cada vez que a avião fez uma viagem:


flights_com_planes %>% 
  filter(dest=="GSO") %>% 
  arrange(tailnum) %>%
  select(tailnum, month, day, dep_time, manufacturer, year.y, model, seats)

Observe que geramos um problema pequeno quando juntamos os dos bancos: ambos os bancos contém uma variável se chama ‘year’. Dado que as variáveis têm que ter nomes únicos, o R renomeou as colunas ‘year.x’ e ‘year.y’. Isto é um pouco chato porque realmente não distingue informação diferente: o ano da viagem (year.x) e o ano da fabricação do voo (year.y). É sempre melhor renomear as variáveis antes da junção para evitar conflitos, ou selecionar apenas as variáveis que você quer transportar de um banco de dados para o outro.


planes <- planes %>%
  rename("year_fabricação"="year")

flights_com_planes <- flights %>% left_join(planes, by=c("tailnum"))

Agora, o nosso novo tibble é pronto para analisar e visualizar como qualquer outro tibble que já vimos. Por exemplo, vamos resumir o número de voos por ano de fabricaçao da avião:


flights_com_planes %>% 
  group_by(year_fabricação) %>%
  tally() %>%
  ggplot() +
  geom_col(aes(x=year_fabricação, y=n))

Espero que você não estava num voo com avião da década 50…

Até agora, assumimos que os dois bancos contém dados completos: que cada avião que existe no banco de dados flights existe no banco de dados planes (não estamos faltando dados), e que cada avião no banco de dados planes existe no banco de dados flights (não temos aviões adicionais que não voaram). Isso é importante para verificar se a junção deu certo.

A função útil para identificar as observações que existem em um banco mas não um outro é anti_join. Ela segue exatamente o mesmo padrão que left_join(), mas o resultado não é um tibble combinado, é as observações do primeiro tibble que não aparecem no segundo.


flights %>% anti_join(planes, by=c("tailnum"))

Observe que temos 52,606 observações em flights que têm um tailnum que não existe em planes. Isso afeta, por exemplo, o nosso gráfico por ano de fabricação do voo, que falta estes voos.

Também é fácil availiar o inverso: se existam aviões que não voaram da Nova Iorque em 2013, invertindo os dois bancos de dados no anti_join().


planes %>% anti_join(flights, by=c("tailnum"))

Boa notícia: todas as aviões de planes são presentes em flights.

Exercício 1: Juntando dois bancos

  1. Crie dois tibbles pequenos (com a função tibble()) como eles abaixo e juntar eles pela coluna ID.
  1. Execute um join dos bancos de dados flights e planes para apenas os voos de carrier United (UA) no dia 16 de setembro de 2013. Qual é o modelo model mais comum destes voos?
  1. Quantos assentos (seats) totais foram instalados nos voos de JFK para Atlanta (ATL) em cada mês?
  1. Queremos um resumo do número de voos no ano de 2013 por companhia aérea. Mas o nosso banco de flights não contém o nome oficial das companhias. Execute um join entre o banco de dados flights e airlines para criar uma tabela resumida e clara.

Juntando Bancos por Múltiplas Variáveis

Considere um outro banco de dados no nycflights13, weather, que contém as condições climáticas em cada aeroporto por hora. Como sabemos o tempo para cada voo? Não é suficiente juntar por ‘origin’, obviamente porque o tempo varia por dia e hora e queremos as condições relevantes na hora da partida do voo. Então é necessário juntar com as variáveis que identificam a unidade de análise comum entre os dois bancos: localização e hora.

Localização é fácil: ‘origin’. Hora é mais complexo: para definir o momento exato temos que considerar ‘year’, ‘month’, ‘day’ e ‘hour’. Idealmente queremos incluir ‘minute’ também que existe no banco flights, mas está faltando no banco de weather. Para incluir todas essas variáveis como identificadores comuns, é só incluir eles no argumento do left_join:


flights_weather <- flights %>% left_join(weather, c("origin", "year", "month", "day", "hour"))

Por interesse, podemos identificar os voos sujeitos a maior velocidade de vento:


flights_weather %>% ungroup() %>% 
  top_n(1, wind_speed)

O que acontece se esquecemos de uma variável relevante? Por exemplo, sem ‘origin’?


flights %>% left_join(weather, c("year", "month", "day", "hour"))

Opa, o nosso banco agora tem mais de uma milhão de linhas! Por que? Lembre-se que com um left_join começamos com o banco na esquerda e, para cada voo, buscamos as linhas no segundo banco (weather) com o mesmo year, month, day e hour. Por exemplo, o primeiro voo no banco de dados flights partiu a hora:


flights %>% slice(1) %>% select(year, month, day, hour)

# A tibble: 1 x 4
   year month   day  hour
  <int> <int> <int> <dbl>
1  2013     1     1     5

Quantas observações no banco de dados de weather batem com estes critérios? Vamos ver:


weather %>% filter(year==2013 & month==1 & day==1 & hour==5)

Três. Um para cadao aeroporto de origem, que esquecemos de incluir como identificador comum. Então quando pedimos para R juntar os dois bancos apenas pelas variáveis de year, month, day e hour, adicionamos três observações de tempo para cada voo, triplicando o tamanho de banco de dados. O resultado é que cada os dados do voo é duplicado três vezes, cada um com dados diferentes de tempo, como mostrado na tabela abaixo. Duas das linhas são erradas porque são os dados de tempo de locais diferentes.


flights %>% left_join(weather, c("year", "month", "day", "hour")) %>% 
  slice(1:3) %>%
  select(year, month, day, hour, dep_time, carrier, flight, humid, wind_speed)

Bancos e Dados Faltantes (right_join, inner_join, outer_join, anti_join)

Que tal se quisemos um banco de dados mais ‘limpo’ no resultado da junção dos dois tibbles, tirando as observações que não tem um par no outro banco? As vezes é útil e eficiente usar a função alternative inner_join(). Ela funciona identicalmente que left_join() exceto que o resultado falta as observações para quais o identificador comum não existe nos dois bancos:


flights %>% inner_join(planes, by=c("tailnum"))

Quantas observações existem no resultado? 284170, a diferença entre o número de observações em flights (336776) e os voos sem tailnum em planes, o resultado de anti_join (52606); pode verificar.

Frequentemente, usando um inner_join gera um viés de seleção - não sabemos muito sobre as aviões faltandas, mas não podemos ignorar eles para uma análise quantitativa. Então sempre recomendamos um left_join() como a ferramenta padrão.

Para mostrar situações mais complexas, vamos trabalhar com um outro banco de dados de nycflights13, os dados de airports, que contém o seu nome completo, localização etc. O primeiro desafio aqui é que temos que decidir como a juntar os bancos? No flights temos duas colunas sobre aeroportos: origin e dest. Lembrando que origin é só os três aeroportos de Nova Iorque, juntando por origin vai gerar muita repetição. Então vamos importar os dados do aeroporto do destino, dest.

O identificador único do tibble airports é faa, que usa o mesmo código official que dest, que é ótimo. Porém, os nomes de colunas são diferentes nos dois bancos: dest e faa. Como resolvemos a diferença? Há várias possibilidades, mas sugerimos renomear a coluna de um banco antes de executar a junção para evitar problemas:


airports <- airports %>% 
  rename(dest=faa)

flights %>% left_join(airports, by=c("dest"))

Outro ponto é que adicionamos muitas novas colunas de airports ao flights que pode poluir a nossa análise/apresentação. As vezes é melhor selecionar as colunas no segundo banco antes de realizar a junção. Por exemplo, se apenas quisemos o nome completo do aeroporto:


flights %>% left_join(airports %>% select(dest, name), 
                      by=c("dest"))

Há mais uma maneira em que os airports são difíceis para juntar: Em contraste com os dados de planes, airports inclui aeroportos que não foram destinos dos voos de flights. Então ambos os anti_joins mostram observações faltando:


flights %>% anti_join(airports, by=c("dest"))
airports %>% anti_join(flights, by=c("dest"))

Observe que o nosso left_join preservou todos os dados de flights e incorporou (como colunas novas) os dados de apenas os aeroportos que são presentes na coluna dest. Ou seja, o left em left_join preserva os dados do tibble na esquerda (o primeiro tibble no código).

Não precisa ser assim: existe também um right_join que preserva os dados do tibble na direita, incluindo todas as observações da direita e apenas eles da esquerda que tem um par na direita. Com o nosso exemplo de flights e airports:


flights %>% right_join(airports, by=c("dest"))

Veja que o número de observações mudou - é menos do que banco de dados de flights. Também note que o resultado contém muitos NA para as variáveis de flights - se você passa para as colunas finais na tabela você vai encontrar os dados dos aeroportos. Mas são aeroportos que não foram destinos dos voos da Nova Iorque, por exemple ‘04G’, ‘Lansdowne Airport’. Então eles foram preservadas no resultado (por causa de right_join) mas sem dados correspondentes de flights. Em contraste com o left_join, perdemos algumas observações de flights que voaram para aeroportos ausentes de banco de dados de airports.

Finalmente, é possível realizar um tipo de junção que preserva as observações de ambos os bancos de dados. Isto sempre vai gerar o banco de dados maior, pois mantemos todas as observações do primeiro banco de dados, e também todas do segundo banco. A função se chama full_join()


flights %>% full_join(airports, by=c("dest"))

Quantas observações agora? 338,133, acima de 336,776 no flights pela quantidade de aeroportos faltando em flights, 1,357 (da anti_join acima).

Resumindo, existem cinco tipos de joins que produzem resultados differentes:

Tipo Resultado
left_join Preservando todas as observações de Banco 1 com as colunas adicionais de Banco 2
right_join Preservando todas as observações de Banco 2 com as colunas adicionais de Banco 1
inner_join Preservando apenas as observações presentes em ambos os Bancos
outer_join Preservando todas as observações dos dois bancos
anti_join Identificar as observações em Banco 1 ausentes em Banco 2

Exercício 2: Joins com Dados Faltantes

  1. Crie dois tibbles pequenos (com a função tibble()) como eles abaixo e juntar eles pelos identificadores comuns.
  1. Queremos dois banco de dados:

  1. Um limpo que contém apenas as unidades com dados completos para valor e população. Use um join apropriado para criar este banco de dados.

  2. Um ‘completo’ que contém todas as unidades mesmo que não temos dados completos. Use um join apropriado para criar este banco de dados.

  1. Usando um join, identifique a precipitatação (precip) média no momento de partido dos voos de LGA em cada dia de dezembro.
  1. A visibilidade afeta o número de partidas por hora?

  1. Usando um join apropriado, gere um banco de dados com a visibilidade em cada hora do ano e o número de voos.

  2. Resumo o seu banco de dados para estimar o número de voos por hora média por valor da variável visibilidade. Mostre os resultados num gráfico de pontos.

gere um gráfico que mostre a correlação entre o número de partidas por hora, e a visibilidade (visib).

Nesting data (nest, unnest)

Agora vamos mudar tópico e discutir um tópico menos ‘essencial’ mas bastante intuitivo e pedagógico que deve te ajudar entender e organizar os seus dados. Até agora, vejamos dois jeitos de ‘agrupar’ dados: (i) com variáveis discretas e o use de group_by() para definir o escopo da transformação/resumo de dados, e (ii) em tibbles diferentes (como flights e airports, usando left_join para juntar os dados).

Existe uma terceira opção que fica no meio do caminho entre os dois: Podemos colocar tibbles separados dentro de um tibble ‘superior’, e identificar cada tibble com uma variável discreta. Um pouco estranho, mas fará muito sentido em breve. O mais fácil é ver um exemplo.


flights_nested <- flights %>% group_by(origin) %>%
  nest()
flights_nested

A lógica do código é usar a mesma linguagem de agrupamento, group_by(), para agrupar por aeroporto de origem, e depois usar a função nest() (sem argumentos) para ‘colapsar’ o resto do tibble por aeroporto de origem. Isso gera um tibble novo para cada aeroporto, que fica na linha apropriada e identificada pela coluna origin no novo tibble.

Para acessar os dados apenas dw ‘EWR’ agora, é só filtrar para a coluna e linha apropriada e vejamos que é uma tabela inteira:


flights_nested %>% filter(origin=="EWR") %>%
  pull(data)

Nesting é ainda mais útil com mais variáveis de agrupamento:


flights_nested <- flights %>% group_by(origin, carrier) %>%
  nest() %>%
  arrange(carrier, origin)
flights_nested

–

Agora temos um tibble com 35 observações em que a unidade de análise é cada aeroporto-companhia aérea, e a coluna ‘data’ contém um tibble dos voos de uma companhia aérea de um aeroporto de origem. Para voltar ao tibble original, é só aplicar unnest.


flights_nested %>% unnest()

Por que o ‘nesting’ é valioso? Nenhuma operação de programação exige nesting, mas o valor é deixar mais claro a estrutura e conteúdo dos nossos dados. Podemos trabalhar explicitamente com a unidade de análise relevante, e esconder a montanha de dados internos. No futuro vamos aprender como a aplicar funções para cada tibble na coluna de ‘data’, que facilita também a repetição de funções complexas por grupo.

Mais um exemplo, combinando nest com left_join, deixando claro a unidade de análise de cada avião, e colapsando as viagens de cada avião na sua própria mini-tibble numa coluna dedicada:


flights %>% group_by(tailnum) %>%
  nest() %>%
  left_join(planes, by=c("tailnum")) %>%
  rename("viagens"="data")

Exercício 3: Dados Nested

  1. Crie um tibble nested, com a unidade de análise cada origem-destino par, e a coluna de dados todos os detalhes dos voos entre aquele origem e destino.
  1. Crie um tibble nested por aeroporto de origem, hora, dia e mês. Use um join para juntar os dados de weather.


Leitura para Tutorial 8

Antes da próxima aula, por favor leia R 4 Data Science, Capítulo